Modern software development teams have adopted a continuous delivery approach based upon DevOps and agile development techniques. The small and frequent code changes that result from such methodologies can deliver significant benefit in terms of reduced lead time for changes, a lower failure rate, and a reduced mean time to recovery when errors are encountered. Indeed, today’s developers migrate more frequent changes into the production environment than ever before.
This would seem to be a good thing—and it can be. However, when you add database change into the mix, things get a bit more challenging. More focus is needed on coupling software/database change both at a technology level and at a personnel level. DBA-level expertise is needed on development teams to help guide and implement changes appropriately. Automated software solutions that combine and coordinate application code and database changes are also needed.
This means that application and database changes must be tightly coupled and orchestrated in tandem for successful DevOps. This brings us to the question posed by the title of this month’s column: What is the role of the DBA in the application development process?
This is a somewhat loaded question. Application folks generally don’t want to turn over too much control of their applications to the DBA. And many DBAs don’t want to get too involved in writing program code. But the two functions need to collaborate in order to produce efficient database applications. And this can be done without stepping on the toes of either the DBA or the application developer.
DBAs must understand and participate in application design, not necessarily the actual coding though. Application design includes database concerns such as interfacing SQL with traditional programming languages and the type of SQL to use. But every aspect of program coding will affect the usability and effectiveness of the application. Furthermore, each application program must be designed to ensure the integrity of the data it modifies.
Designing a proper database application system is a complex and time-consuming task, and the choices made during that process will impact the usefulness of the final delivered application. An improperly designed and coded application may need to be redesigned and re-coded from scratch if it is inefficient, ineffective, or not easy to use. To properly design an application that relies on databases for persistent data storage, the system designer must match the application development languages and tools to the physical database design and the functionality of the DBMS being used. The first thing to be mastered, though, must be a sound understanding of SQL. And, unfortunately, many application folks just do not have enough SQL knowledge to formulate the most efficient query in every situation. SQL is changing and growing, and the DBA will usually have current and in-depth knowledge of what is possible and what SQL formulations perform best. The application developer and the DBA need to work as a team to make sure that this knowledge gets built into each and every database application.
Access paths, the internal instructions used by the DBMS to actually access data, are another critical area where DBA guidance can help. The database optimizer converts SQL into access paths, and any time the SQL changes, the access path for the statement may also change. And that can dramatically alter the performance of the application. Oversight is crucial. As such, some organizations require the DBA group to review and approve all SQL changes to identify potential access path problems. DBA review of all SQL changes before they are migrated to production is a reasonable precaution, especially for organizations with developers that are not skilled at SQL coding and access path interpretation. Automated tools that examine and report on changes and proffer guidance are useful here, particularly in an automated DevOps culture.
Of course, there are other issues, such as the interface to the programming language, drivers, procedures for compilation, and so on. But let’s just summarize by saying the DBA needs to be involved in application design to ensure efficient database usage—and therefore efficient applications.
And be sure to involve the DBAs in all of the design reviews for the code too.